USE [NANS21V]
GO
/****** オブジェクト:  StoredProcedure [dbo].[GetRelayMember]    スクリプト日付: 01/22/2026 10:54:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetRelayMember]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetRelayMember]
GO

CREATE PROCEDURE [dbo].[GetRelayMember]
	@TAIKAINO int
AS
select k.TAIKAINO,
	k.KYOGISYANO as TeamNo,-- チーム
	k.KYOGISYAMEI as TeamName,
	k2.NUMBER2 as No,-- 競技者
	k2.KYOGISYAMEI as Name,
	k2.KYOGISYAKANA as Kana,
    dbo.Seibetu(K2.SEIBETU) AS SEIBETU,
	(
		CONVERT(varchar, k.TAIKAINO) + '-' + 
        CONVERT(varchar, l.KYOGINO) + '-' + 
        CONVERT(varchar, l.ROUNDNO) + '-' + 
        CONVERT(varchar, l.SUBNO) + '-' + 
		CONVERT(varchar, k.KYOGISYANO)
    ) AS KEKKAFLG 
from TBL_KYOGISYA k 
	left join TBL_TEAM t on k.TAIKAINO = t.TAIKAINO and k.KYOGISYANO = t.TEAMNO 
		left join TBL_KYOGISYA k2 on t.TAIKAINO = k2.TAIKAINO and t.KYOGISYANO = k2.KYOGISYANO
	left join TBL_LANE l on k.TAIKAINO = l.TAIKAINO and k.KYOGISYANO = l.KYOGISYANO 
where k.TAIKAINO = @TAIKAINO and k.KYOGISYAFLG = '1' 
-- 並び順は競技→チーム→競技者
order by l.KYOGINO,l.SUBNO,l.ROUNDNO,k.KYOGISYANO,t.ID,k2.KYOGISYANO;